<?php
/**
 * Step 1: Require the Slim Framework
 *
 * If you are not using Composer, you need to require the
 * Slim Framework and register its PSR-0 autoloader.
 *
 * If you are using Composer, you can skip this step.
 */
require 'Slim/Slim.php';

\Slim\Slim::registerAutoloader();

/**
 * Step 2: Instantiate a Slim application
 *
 * This example instantiates a Slim application using
 * its default settings. However, you will usually configure
 * your Slim application now by passing an associative array
 * of setting names and values into the application constructor.
 */
$app = new \Slim\Slim();

/**
 * Step 3: Define the Slim application routes
 *
 * Here we define several Slim application routes that respond
 * to appropriate HTTP request methods. In this example, the second
 * argument for `Slim::get`, `Slim::post`, `Slim::put`, and `Slim::delete`
 * is an anonymous function.
 */

// GET route
$app->get('/', function () {
    $template = <<<EOT
<!DOCTYPE HTML>
<html>
<head>
    <title>Cellar</title>
    <link rel="stylesheet" href="css/styles.css"/>
</head>

<body>

<div class="header">
    <input type="text" id="searchKey"/>
    <button id="btnSearch">Search</button>
    <button id="btnAdd">New Wine</button>
</div>


<div class="leftArea">
    <ul id="wineList"></ul>
</div>

<form id="wineForm">

    <div class="mainArea">

        <label>Title:</label>
        <input type="text" id="title" name="name" required>

        <label>Image:</label>
        <input type="text" id="image" name="grapes"/>

        <label>Comments:</label>
        <input type="text" id="comments" name="comments"/>

        <label>Temperature:</label>
        <input type="text" id="temperature" name="temperature"/>

        <label>Weather Condition:</label>
        <input type="text" id="conditionCode" name="conditionCode"/>

        <button id="btnSave">Save</button>
        <button id="btnDelete">Delete</button>

    </div>

    <div class="rightArea">

    </div>

</form>

<script src="js/jquery-1.7.1.min.js"></script>
<script src="js/main.js"></script>

</body>
</html>
EOT;
    echo $template;
});

// POST route
$app->post('/post', function () {
    echo 'This is a POST route';
});

// PUT route
$app->put('/put', function () {
    echo 'This is a PUT route';
});

// DELETE route
$app->delete('/delete', function () {
    echo 'This is a DELETE route';
});

$app->get("/propositions", function(){
    echo 'Proposition route';
});

$app->get("/proposition/:wcond/:temperature", 'getProposition');

$app->get('/wines', 'getWines');
$app->get('/wines/:id',	'getWine');
$app->get('/wines/search/:query', 'findByName');
$app->post('/wines', 'addWine');
$app->put('/wines/:id', 'updateWine');
$app->delete('/wines/:id',	'deleteWine');


function getProposition($weatherCondition, $temperature){
    $query = "select * from propositions where weather_condition_id=:weatherCondition";
    try {
        $db = getConnection();
        $stmt = $db->query($query);
        $wines = $stmt->fetchAll(PDO::FETCH_OBJ);
        $db = null;
        echo '{"wine": ' . json_encode($wines) . '}';
    } catch(PDOException $e) {
        echo '{"error":{"text":'. $e->getMessage() .'}}';
    }
}

function insertProposition(){

    $request = Slim::getInstance()->request();
    $proposition = json_decode($request->getBody());

    $query = "INSERT INTO webyweather.propositions (title, image, user_comments)
              VALUES (:title, :image, :comments)";

    $queryForTemperature = "insert into webyweather.propositions_temperatures (proposition_id, temperature_id)
                            values (:propositionId, (select t.id from webyweather.temperatures t where t.value = :temperature))";

    $queryForWeatherCond = "insert into webyweather.propositions_weather_conditions (proposition_id, weather_condition_id)
                            values (:propositionId, (select wc.id from webyweather.weather_conditions wc where wc.code = :conditionCode))";
    try {
        $db = getConnection();
        $db->beginTransaction();

        $stmt = $db->prepare($query);
        $stmt->bindParam("title", $proposition->title);
        $stmt->bindParam("image", $proposition->image);
        $stmt->bindParam("user_comments", $proposition->comments);
        $stmt->execute();
        $stmt = null;
        $propositionId = $db->lastInsertId();

        $stmt = $db->prepare($queryForTemperature);
        $stmt->bindParam("propositionId", $propositionId);
        $stmt->bindParam("temperature", $proposition->temperature);
        $stmt->execute();

        $stmt = null;
        $stmt = $db->prepare($queryForWeatherCond);
        $stmt->bindParam("propositionId", $propositionId);
        $stmt->bindParam("conditionCode", $proposition->conditionCode);
        $stmt->execute();

        $db->commit();
        $db = null;
        echo json_encode($proposition);
    } catch(PDOException $e) {
        error_log($e->getMessage(), 3);
        echo '{"error":{"text":'. $e->getMessage() .'}}';
    }
}

function getWines() {
    $sql = "select * FROM wine ORDER BY name";
    try {
        $db = getConnection();
        $stmt = $db->query($sql);
        $wines = $stmt->fetchAll(PDO::FETCH_OBJ);
        $db = null;
        echo '{"wine": ' . json_encode($wines) . '}';
    } catch(PDOException $e) {
        echo '{"error":{"text":'. $e->getMessage() .'}}';
    }
}

function getWine($id) {
    $sql = "SELECT * FROM wine WHERE id=:id";
    try {
        $db = getConnection();
        $stmt = $db->prepare($sql);
        $stmt->bindParam("id", $id);
        $stmt->execute();
        $wine = $stmt->fetchObject();
        $db = null;
        echo json_encode($wine);
    } catch(PDOException $e) {
        echo '{"error":{"text":'. $e->getMessage() .'}}';
    }
}

function addWine() {
    error_log('addWine\n', 3, '/var/tmp/php.log');
    $request = Slim::getInstance()->request();
    $wine = json_decode($request->getBody());
    $sql = "INSERT INTO wine (name, grapes, country, region, year, description) VALUES (:name, :grapes, :country, :region, :year, :description)";
    try {
        $db = getConnection();
        $stmt = $db->prepare($sql);
        $stmt->bindParam("name", $wine->name);
        $stmt->bindParam("grapes", $wine->grapes);
        $stmt->bindParam("country", $wine->country);
        $stmt->bindParam("region", $wine->region);
        $stmt->bindParam("year", $wine->year);
        $stmt->bindParam("description", $wine->description);
        $stmt->execute();
        $wine->id = $db->lastInsertId();
        $db = null;
        echo json_encode($wine);
    } catch(PDOException $e) {
        error_log($e->getMessage(), 3, '/var/tmp/php.log');
        echo '{"error":{"text":'. $e->getMessage() .'}}';
    }
}

function updateWine($id) {
    $request = Slim::getInstance()->request();
    $body = $request->getBody();
    $wine = json_decode($body);
    $sql = "UPDATE wine SET name=:name, grapes=:grapes, country=:country, region=:region, year=:year, description=:description WHERE id=:id";
    try {
        $db = getConnection();
        $stmt = $db->prepare($sql);
        $stmt->bindParam("name", $wine->name);
        $stmt->bindParam("grapes", $wine->grapes);
        $stmt->bindParam("country", $wine->country);
        $stmt->bindParam("region", $wine->region);
        $stmt->bindParam("year", $wine->year);
        $stmt->bindParam("description", $wine->description);
        $stmt->bindParam("id", $id);
        $stmt->execute();
        $db = null;
        echo json_encode($wine);
    } catch(PDOException $e) {
        echo '{"error":{"text":'. $e->getMessage() .'}}';
    }
}

function deleteWine($id) {
    $sql = "DELETE FROM wine WHERE id=:id";
    try {
        $db = getConnection();
        $stmt = $db->prepare($sql);
        $stmt->bindParam("id", $id);
        $stmt->execute();
        $db = null;
    } catch(PDOException $e) {
        echo '{"error":{"text":'. $e->getMessage() .'}}';
    }
}

function findByName($query) {
    $sql = "SELECT * FROM wine WHERE UPPER(name) LIKE :query ORDER BY name";
    try {
        $db = getConnection();
        $stmt = $db->prepare($sql);
        $query = "%".$query."%";
        $stmt->bindParam("query", $query);
        $stmt->execute();
        $wines = $stmt->fetchAll(PDO::FETCH_OBJ);
        $db = null;
        echo '{"wine": ' . json_encode($wines) . '}';
    } catch(PDOException $e) {
        echo '{"error":{"text":'. $e->getMessage() .'}}';
    }
}

function getConnection() {
    $dbhost="localhost";
    $dbuser="webyweather";
    $dbpass="k@1RkhmMm!#";
    $dbname="cellar";
    $dbh = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    return $dbh;
}

/**
 * Step 4: Run the Slim application
 *
 * This method should be called last. This executes the Slim application
 * and returns the HTTP response to the HTTP client.
 */
$app->run();
